import socket
from concurrent.futures import ThreadPoolExecutor
from datetime import datetime
import configparser
import os
import sys
from database.db_tools import SQLServerDatabase, connection_string
IDLE = 0
TESTING = 1
TEST_OK = 2
TEST_NG = 3

def get_current_datetime():
    """获取当前日期和时间的字符串表示"""
    return datetime.now().strftime("%Y-%m-%d %H:%M:%S")

def log_message(msg, file_path):
    """记录消息到文件"""
    with open(file_path, "a", encoding="utf-8") as log_file:
        log_file.write(msg)

def setup_logging():
    """设置日志文件目录和文件名"""
    today_date = datetime.now()
    folder_path = os.path.join("log", str(today_date.year), f"{today_date.month}月", f"{today_date.day}")

    if not os.path.exists(folder_path):
        os.makedirs(folder_path)

    return os.path.join(folder_path, f"日志信息.txt")

def get_server_address():
    """获取服务器IP"""
    ip = socket.gethostbyname(socket.gethostname())
    return ip,



# 将设备发送的订单号和sn存入数据库
def save_sn_order(request):
    try:
      # 操作数据库
      with SQLServerDatabase(connection_string) as db:
          # values = [str(request).split(";")[1],str(request).split(";")[2]]
          # columns = ['orderNumber', 'sn']
          # print(request)
          serialNumber = str(str(request).split(' ')[1]).replace(';', '').replace('\r\n', '')
          values = [serialNumber]
          columns = ['sn']
          # print('values',values)
          # print('columns',columns)
          res = db.execute_insert("ts_auto_device_equipemt", values=values, columns=columns)
          print('保存成功', res)
    except Exception as e:
          values = [str(request)]
          columns = ['msg']
          res = db.execute_insert("ts_auto_device_equipemt_expection_msg", values=values, columns=columns)

# 查询数据库中是否存在该sn的结果，并返回
def get_results_sn_order_number(executeRequestMsg):
    try:

        # orderNumber = executeRequestMsg.split(' ')[1]
        serialNumber = str(str(executeRequestMsg).split(' ')[1]).replace(';', '').replace('\r\n', '')

        # 操作数据库
        with SQLServerDatabase(connection_string) as db:
          dateRes = db.execute_query_all("ts_auto_device_equipemt",
            filters=f"sn = ? ORDER BY id DESC",
            params=[serialNumber])
          filterTime = ''
          if len(dateRes['data']) > 0:
            filterTime = dateRes['data'][0]['currentTime']
            print(filterTime)
      
          res = db.execute_query_all("ts_recheck_res",
              # filters=f"orderNumber = ? AND binSerialNumber = ?  AND isFinal = 1 AND isAuto = 1 ORDER BY id , checkRes DESC",
              # params=[orderNumber, serialNumber],
              filters=f"binSerialNumber = ?  AND recheckTime > ? AND isFinal = 1 AND isAuto = 1 ORDER BY id , shareRes DESC",
              params=[serialNumber,filterTime],
              customSelectColumns= 'TOP 1 shareRes')
          # print('复检结果查询：',res)
          print('复检结果查询：',res['data'])
          if len(res['data']) > 0:
              if res['data'][0]['shareRes'] == '1':
                return  TEST_OK
              else:
                return TEST_NG
          else:
              return TESTING
    except Exception as e:
          values = [executeRequestMsg]
          columns = ['msg']
          res = db.execute_insert("ts_auto_device_equipemt_expection_msg", values=values, columns=columns)
          return IDLE



def handle_client(client_socket, address):
    file_path = setup_logging()

    try:

        while True:
            current_time = get_current_datetime()

            request = client_socket.recv(1024).decode("utf-8")
            msg = f" {current_time}-----客户端----->发送 {address} : {request}\r\n"
            print(msg)
            log_message(msg, file_path)

            if not request:
                current_time = get_current_datetime()
                msg = f" {current_time}-----客户端-----{address} 断开连接."
                print(msg)
                log_message(msg, file_path)
                break

            if "ready?" in request:
                response = "ready yes;\r\n"
                client_socket.send(response.encode("utf-8"))
                current_time = get_current_datetime()
                msg = f" {current_time}-----服务端----->回复 {address} : {response}\r\n"
                print(msg)
                log_message(msg, file_path)

            if "execute" in request:
                current_time = get_current_datetime()
                print(f' {current_time}-----服务端----->收到：{request}\r\n')
                response = "execute " + str(str(request).split(' ')[1]).replace(';', '').replace('\r\n', '') + " yes;\r\n"
                client_socket.send(response.encode("utf-8"))
                # client_socket.send(response.encode("utf-8"))
                current_time = get_current_datetime()
                msg = f" {current_time}-----服务端----->回复 {address} : {response}\r\n"
                print(msg)
                log_message(msg, file_path)
                executeRequestMsg = str(request) #
                save_sn_order(executeRequestMsg)

            # if executeRequestMsg is None:
            #   executeRequestMsg = ''

            if "test status" in request:
                # response = f"test status: t0;\r\n"
                # client_socket.send(response.encode("utf-8"))
              try: 
                if executeRequestMsg:
                    # 创建配置文件解析器
                    config = configparser.ConfigParser()
                    # \ufeff 是 UTF-8 文件的字节顺序标记 (BOM)
                    # 使用 encoding='utf-8-sig' 来处理潜在的 BOM
                    # 读取配置文件
                    os.chdir(os.path.dirname(os.path.abspath(sys.argv[0])))
                    config.read('configs.ini', encoding='utf-8-sig')

                    tempPath = config['pathConfig']['tempPath']


                    # 使用'with'语句打开文件，模式为'r'表示读取
                    with open(tempPath, 'r') as file:
                        # 读取文件的所有内容
                        tempContent = file.read()
                        msg = f" {current_time}-----读取temp文件内容----->{tempContent}\r\n"
                        log_message(msg, file_path)

                    if len(tempContent.split(":")) == 2 and tempContent.split(":")[0] == str(str(executeRequestMsg).split(' ')[1]).replace(';', '').replace('\r\n', ''):
                      response = f"test status {TEST_OK if tempContent.split(':')[1] == 'True' else TEST_NG}\r\n"
                      client_socket.send(response.encode("utf-8"))
                      current_time = get_current_datetime()
                      msg = f" {current_time}-----服务端----->回复 {address} : {response}\r\n"
                      print(msg)
                      log_message(msg, file_path)          
                    else:
                      # str(str(executeRequestMsg).split(' ')[1]).replace(';', '').replace('\r\n', '')
                      res = get_results_sn_order_number(executeRequestMsg)

                      if res == 1:
                        with open(tempPath, 'r') as file:
                        # 读取文件的所有内容
                          tempContent = file.read()
                          # msg = f" {current_time}-----读取temp文件内容----->{tempContent}\r\n"
                          # log_message(msg, file_path)
                        if len(tempContent.split(":")) == 2 and tempContent.split(":")[0] == str(str(executeRequestMsg).split(' ')[1]).replace(';', '').replace('\r\n', ''):
                          response = f"test status {TEST_OK if tempContent.split(':')[1] == 'True' else TEST_NG}\r\n"
                          client_socket.send(response.encode("utf-8"))
                          current_time = get_current_datetime()
                          msg = f" {current_time}-----服务端----->回复 {address} : {response}\r\n"
                          print(msg)
                          log_message(msg, file_path)  
                        else:
                          response = f"test status {res}\r\n"
                          client_socket.send(response.encode("utf-8"))
                          current_time = get_current_datetime()
                          msg = f" {current_time}-----服务端----->回复 {address} : {response}\r\n"
                          print(msg)
                          log_message(msg, file_path)
                      else:
                        msg = f" {current_time}-----数据库获取----->{tempContent}\r\n"
                        log_message(msg, file_path)
                        response = f"test status {res}\r\n"
                        client_socket.send(response.encode("utf-8"))
                        current_time = get_current_datetime()
                        msg = f" {current_time}-----服务端----->回复 {address} : {response}\r\n"
                        print(msg)
                        log_message(msg, file_path)
                else:
                  response = f"test status 0\r\n"
                  client_socket.send(response.encode("utf-8"))
                  current_time = get_current_datetime()
                  msg = f" {current_time}-----服务端----->回复 {address} : {response}\r\n"
                  print(msg)
                  log_message(msg, file_path)
              except Exception as e:
                print('未发送execute命令')
                response = f"test status 0\r\n"
                client_socket.send(response.encode("utf-8"))
                current_time = get_current_datetime()
                msg = f" {current_time}-----服务端----->回复 {address} : {response}\r\n"
                print(msg)
                log_message(msg, file_path)
    finally:
        client_socket.close()

def start_server():
    ip = get_server_address()
    print(f'服务器初始化成功，IPv4地址：{ip}，端口号：9001')


    with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as server_socket:
        server_socket.bind(("0.0.0.0", 9001))
        server_socket.listen(10)

        with ThreadPoolExecutor() as executor:
            while True:
                client_socket, addr = server_socket.accept()
                print(f"收到客户端连接： {addr}")
                executor.submit(handle_client, client_socket, addr)

if __name__ == "__main__":
    start_server()
